Migration using a 2nd SQL server as the database

This topic is related to sections:

• The Data Migration Process

• Migration using SAP HANA as the database

• Migration using a Oracle as the database

About Using a 2nd SQL Server as the database

When the project uses a 2nd SQL Server as the migration database, all of the transformation of that data takes place within the non Migration SQL Server database.  Migration is stored within 1st SQL Server as well as the CONSTRUCT Tables.  These notes are very important when using a 2nd SQL Server as the migration database:

• The Migration database runs from 1st SQL Server and the Working databases run from 2nd SQL Server - all use dbo as the SCHEMA.

• Migration runs all applications from 1st SQL Server but does not store any of the rows of data

• Working database SQL Server stores all Data for Snapshot (src, tgt, or wrk) databases - this includes a SRCCONSTRUCT database and a REPORT database

• The 2nd SQL Server is setup in Administer > Setup > Servers as a separate server of type SQLSERVER

Preparation for using a 2nd SQL Server as Database

SRCCONSTRUCT Database Build in 2nd SQL Server

The team creates CONSTRUCT as a Snapshot Source Datasource (SRCCONSTRUCT) within the 2nd SQL Server database rather than using the one delivered with Syniti Migrate.  This new Snapshot datasource stores the CONSTRUCT table data for use during Mock runs as the table data remains static. Each template table built within Syniti Migrate and stored within SQL Server CONSTRUCT database needs to be added to the SRCCONSTRUCT datasource for the 2nd SQL Server, and the Metadata is imported as well. 

This Source Snapshot is connected to the CONSTRUCT datasource that comes delivered and points to the Syniti Migrate system CONSTRUCT database in SQL Server.  As each table is added to the Source Snapshot datasource, the system attempts to import the Metadata (Columns, etc.)

As with any Snapshot Datasource, the SRCCONSTRUCT datasource is built out for XML.  The new tables automatically generate within the 2nd SQL Server, and the Import Project and all tables are scripted in XML for import to Data Services. 

Report Database in 2nd SQL Server

During the refresh of reports, the rows of data are saved to the Report of the same ID as stored within the Report details page.  These reports are all stored in the REPORT database in the Working Database (client's SQL Server DB). 

The REPORT database is stored within the 2nd SQL Server, and the migration of data using DataServices as the ETL generates the report tables and store them within this REPORT db on 2nd SQL Server.  The Parameters page designates the appropriate datasource for reports, and the Datasource REPORT points to the 2nd SQL Server.  As the object goes through the job run, the reports generate as tables within this 2nd SQL Server - REPORT for the static report data. 

Parameters

The Parameters for Working Database Type is SQLSERVER.  Since the REPORT datasource is pointing to the 2nd SQL Server as the Server value, the reports all generate within the 2nd SQL Server REPORT DB as expected.